using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using NBear.Common;

namespace NBear.Data
{
    public enum DatabaseType
    {
        SqlServer = 0,
        MsAccess = 1,
        Other = 2,
        SqlServer9 = 3,
        Oracle = 4,
        MySql = 5
    }

    public class Gateway
    {
        #region Default Gateway

        public static Gateway Default;

        public static void SetDefaultDatabase(DatabaseType dt, string connStr)
        {
            if (dt == DatabaseType.Other)
            {
                throw new NotSupportedException("Please use \"SetDefaultDatabase(string assemblyName, string className, string connStr)\" for databases other than SqlServer, MsAccess, MySql or Oracle Database!");
            }

            DbProvider provider = CreateDbProvider(dt, connStr);

            Default = new Gateway(new Database(provider));
        }

        private static DbProvider CreateDbProvider(DatabaseType dt, string connStr)
        {
            DbProvider provider = null;
            if (dt == DatabaseType.SqlServer9)
            {
                provider = DbProviderFactory.CreateDbProvider(null, typeof(NBear.Data.SqlServer9.SqlDbProvider9).FullName, connStr);
            }
            else if (dt == DatabaseType.SqlServer)
            {
                provider = DbProviderFactory.CreateDbProvider(null, typeof(NBear.Data.SqlServer.SqlDbProvider).FullName, connStr);
            }
            else if (dt == DatabaseType.Oracle)
            {
                provider = DbProviderFactory.CreateDbProvider(null, typeof(NBear.Data.Oracle.OracleDbProvider).FullName, connStr);
            }
            else if (dt == DatabaseType.MySql)
            {
                provider = DbProviderFactory.CreateDbProvider(null, typeof(NBear.Data.MySql.MySqlDbProvider).FullName, connStr);
            }
            else  //Ms Access
            {
                provider = DbProviderFactory.CreateDbProvider(null, typeof(NBear.Data.MsAccess.AccessDbProvider).FullName, connStr);
            }
            return provider;
        }

        public static void SetDefaultDatabase(string assemblyName, string className, string connStr)
        {
            DbProvider provider = DbProviderFactory.CreateDbProvider(assemblyName, className, connStr);
            if (provider == null)
            {
                throw new NotSupportedException(string.Format("Cannot construct DbProvider by specified parameters: {0}, {1}, {2}",
                    assemblyName, className, connStr));
            }

            Default = new Gateway(new Database(provider));
        }

        public static void SetDefaultDatabase(string connStrName)
        {
            DbProvider provider = DbProviderFactory.CreateDbProvider(connStrName);
            if (provider == null)
            {
                throw new NotSupportedException(string.Format("Cannot construct DbProvider by specified ConnectionStringName: {0}", connStrName));
            }

            Default = new Gateway(new Database(provider));
        }

        #endregion

        #region Private Members

        private Database db;

        private void InitGateway(Database db)
        {
            this.db = db;
        }

        private IEntityType[] DoSelectPage<IEntityType>(string orderBy, string where, object[] paramValues, int pageSize, int pageNo, string[] primaryKeys) where IEntityType : IEntity
        {
            string combinedWhere = BuildCombinedWhere<IEntityType>(where);
            IPageSplit ps = SimpleDbHelper.SelectPageSplit(db, ParseTableName(Entity<IEntityType>.GetTableName()), Entity<IEntityType>.GetColumnNames(), combinedWhere, orderBy, primaryKeys[0], paramValues);
            ps.PageSize = pageSize;
            IDataReader reader = ps.GetPageReadOnly(pageNo);
            IEntityType[] objs = EntityFactory<IEntityType>.CreateObjectList(reader, true);
            reader.Close();
            return objs;
        }

        private static string BuildCombinedWhere<IEntityType>(string where) 
            where IEntityType : IEntity
        {
            string additionalWhere = Entity<IEntityType>.GetTableAdditionalWhere();
            string combinedWhere = (where == null ? additionalWhere : (additionalWhere == null ? where : where + " AND " + additionalWhere));
            return combinedWhere;
        }

        private static string ParseTableName(string name)
        {
            return name.Replace("_nbsp_", " ");
        }

        private void DoUpdate<IEntityType>(string[] columns, object[] values, object id, DbTransaction tran, string[] primaryKeys)
            where IEntityType : IEntity
        {
            string tableName = Entity<IEntityType>.GetUpdateTableName() ?? Entity<IEntityType>.GetTableName();

            SimpleDbHelper.Update(db, ParseTableName(tableName), columns, values,
                BuildCombinedWhere<IEntityType>(BuildDbColumnName(primaryKeys[0]) + " = " + BuildDbParamName(primaryKeys[0])), new object[] { id }, tran);
        }

        private void DoUpdate<IEntityType>(string[] columns, object[] values, string where, object[] whereValues, DbTransaction tran)
            where IEntityType : IEntity
        {
            string tableName = Entity<IEntityType>.GetUpdateTableName() ?? Entity<IEntityType>.GetTableName();

            SimpleDbHelper.Update(db, ParseTableName(tableName), columns, values,
                BuildCombinedWhere<IEntityType>(where), whereValues, tran);
        }

        private void DoUpdate<IEntityType>(IEntityType obj, DbTransaction tran, string[] exceptColumns, string[] primaryKeys) 
            where IEntityType : IEntity
        {
            string tableName = Entity<IEntityType>.GetUpdateTableName() ?? Entity<IEntityType>.GetTableName();
            string[] updateColumns = Entity<IEntityType>.GetUpdatableColumnNames(exceptColumns);

            if (updateColumns == null || updateColumns.Length <= 0)
            {
                return;
            }

            string where = "";
            foreach (string key in primaryKeys)
            {
                where += BuildDbColumnName(key) + " = " + BuildDbParamName(key);
                if (key != primaryKeys[primaryKeys.Length - 1])
                {
                    where += " and ";
                }
            }

            SimpleDbHelper.Update(db, ParseTableName(tableName), updateColumns, obj.GetKeyValues().GetValues(updateColumns), BuildCombinedWhere<IEntityType>(where), obj.GetKeyValues().GetValues(primaryKeys), tran);
        }

        private string[] GuessPrimaryKeysForSelectPage<IEntityType>()
            where IEntityType : IEntity
        {
            string[] keys = new string[1];

            //check name = ID or GUID column first
            foreach (string key in Entity<IEntityType>.GetColumnNames())
            {
                if (key.ToUpper() == "ID" || key.ToUpper() == "GUID")
                {
                    keys[0] = key;
                    return keys;
                }
            }

            //check the first ends with ID or Guid column
            foreach (string key in Entity<IEntityType>.GetColumnNames())
            {
                if (key.ToUpper().EndsWith("ID") || key.ToUpper().EndsWith("GUID"))
                {
                    keys[0] = key;
                    return keys;
                }
            }

            //or threat the first column as key column
            keys[0] = Entity<IEntityType>.GetColumnNames()[0];
            return keys;
        }

        private object SelectAggregateValue<IEntityType>(string where, object[] paramValues, string column) where IEntityType : IEntity
        {
            string tableName = Entity<IEntityType>.GetTableName();
            if (tableName.Contains("#_Begin#"))
            {
                tableName = db.DbProvider.FilterSelectSqlBeginEnd(tableName);
            }
            else
            {
                tableName = BuildDbColumnName(tableName);
            }
            string combinedWhere = BuildCombinedWhere<IEntityType>(where);
            object obj = SimpleDbHelper.SelectScalar(db, string.Format("select {2} from {0} {1}", tableName, string.IsNullOrEmpty(combinedWhere) ? "" : "where " + combinedWhere, column), paramValues);
            return obj;
        }

        private string ParseConcreteWhereOrderBy(string whereStr)
        {
            return PropertyItem.ParseExpressionByMetaData(whereStr, new PropertyToColumnMapHandler(Entity<IEntity>.GetPropertyMappedColumn), db.DbProvider.LeftToken, db.DbProvider.RightToken, db.DbProvider.ParamPrefix);
        }

        #endregion

        #region Constructors

        static Gateway()
        {
            if (Database.Default != null)
            {
                Default = new Gateway(Database.Default);
            }
        }

        public Gateway(string connStrName)
        {
            InitGateway(new Database(DbProviderFactory.CreateDbProvider(connStrName)));
        }

        public Gateway(Database db)
        {
            InitGateway(db);
        }

        public Gateway(DatabaseType dt, string connStr)
        {
            if (dt == DatabaseType.Other)
            {
                throw new NotSupportedException("Please use \"new Gateway(string assemblyName, string className, string connStr)\" for databases other than SqlServer, MsAccess, MySql or Oracle Database!");
            }

            DbProvider provider = CreateDbProvider(dt, connStr);

            InitGateway(new Database(provider));
        }

        public Gateway(string assemblyName, string className, string connStr)
        {
            DbProvider provider = DbProviderFactory.CreateDbProvider(assemblyName, className, connStr);
            if (provider == null)
            {
                throw new NotSupportedException(string.Format("Cannot construct DbProvider by specified parameters: {0}, {1}, {2}",
                    assemblyName, className, connStr));
            }

            InitGateway(new Database(provider));
        }

        #endregion

        #region Database

        public static void SetSqlLogger(LogHandler handler)
        {
            Database.SqlLogger = handler;
        }

        public Database Db
        {
            get
            {
                return this.db;
            }
        }

        public DbTransaction BeginTransaction()
        {
            return db.BeginTransaction();
        }

        public DbTransaction BeginTransaction(IsolationLevel il)
        {
            return db.BeginTransaction(il);
        }

        public void CloseTransaction(DbTransaction tran)
        {
            if (tran.Connection != null && tran.Connection.State != ConnectionState.Closed)
            {
                db.CloseConnection(tran);
            }
        }

        public string BuildDbParamName(string name)
        {
            Check.Require(name != null, "Arguments error.", new ArgumentNullException("name"));

            return db.DbProvider.BuildParameterName(name);
        }

        public string BuildDbColumnName(string name)
        {
            Check.Require(name != null, "Arguments error.", new ArgumentNullException("name"));

            return db.DbProvider.BuildColumnName(name);
        }

        #endregion

        #region Batch Gateway

        public Gateway BeginBatchGateway(int batchSize)
        {
            Gateway gateway = new Gateway(new Database(db.DbProvider));
            gateway.BeginBatch(batchSize);
            return gateway;
        }

        public void BeginBatch(int batchSize)
        {
            db.BeginBatchConnection(batchSize >= 1 ? batchSize : 1);
        }

        public void EndBatch()
        {
            try
            {
                db.EndBatchConnection();
            }
            catch
            {
            }
        }

        #endregion

        #region StrongTyped Gateways

        public IEntityType Get<IEntityType>(object id)
            where IEntityType : IEntity
        {
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Get<IEntityType>(object id) only supports entities with single primary key.");
            }
            IDataReader reader = SimpleDbHelper.SelectReadOnly(
                db, ParseTableName(Entity<IEntityType>.GetTableName()), Entity<IEntityType>.GetColumnNames(),
                BuildCombinedWhere<IEntityType>(BuildDbColumnName(primaryKeys[0]) + " = " + BuildDbParamName(primaryKeys[0])), new object[] { id }, null);
            if (!reader.Read())
            {
                return default(IEntityType);
            }
            IEntityType obj = EntityFactory<IEntityType>.CreateObject(reader, true);
            reader.Close();
            return obj;
        }

        public IEntityType Get<IEntityType>(string where, object[] paramValues)
            where IEntityType : IEntity
        {
            IDataReader reader = SimpleDbHelper.SelectReadOnly(
                db, ParseTableName(Entity<IEntityType>.GetTableName()), Entity<IEntityType>.GetColumnNames(), BuildCombinedWhere<IEntityType>(where), paramValues, null);
            if (!reader.Read())
            {
                return default(IEntityType);
            }
            IEntityType obj = EntityFactory<IEntityType>.CreateObject(reader, true);
            reader.Close();
            return obj;
        }

        public IEntityType Get<IEntityType>(WhereClip where)
            where IEntityType : IEntity
        {
            return Get<IEntityType>(ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public bool Exists<IEntityType>(object id)
            where IEntityType : IEntity
        {
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Exists<IEntityType>(object id) only supports entities with single primary key.");
            }
            object retVal = SelectScalar<IEntityType, object>(
                primaryKeys[0], BuildDbColumnName(primaryKeys[0]) + " = " + BuildDbParamName(primaryKeys[0]), new object[] { id });
            return (retVal != null);
        }

        public bool Exists<IEntityType>(string where, object[] paramValues)
            where IEntityType : IEntity
        {
            object retVal = SelectScalar<IEntityType, object>(GuessPrimaryKeysForSelectPage<IEntityType>()[0], where, paramValues);
            return (retVal != null);
        }

        public bool Exists<IEntityType>(WhereClip where)
            where IEntityType : IEntity
        {
            return Exists<IEntityType>(ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public void Delete<IEntityType>(object id)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot delete.");
            }

            string tableName = Entity<IEntityType>.GetUpdateTableName() ?? Entity<IEntityType>.GetTableName();
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Delete<IEntityType>(object id) only supports entities with single primary key.");
            }
            SimpleDbHelper.Delete(db, ParseTableName(tableName),
                BuildCombinedWhere<IEntityType>(BuildDbColumnName(primaryKeys[0]) + " = " + BuildDbParamName(primaryKeys[0])), new object[] { id });
        }

        public void Delete<IEntityType>(object id, DbTransaction tran)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot delete.");
            }

            string tableName = Entity<IEntityType>.GetUpdateTableName() ?? Entity<IEntityType>.GetTableName();
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Delete<IEntityType>(object id, DbTransaction tran) only supports entities with single primary key.");
            }
            SimpleDbHelper.Delete(db, ParseTableName(tableName),
                BuildCombinedWhere<IEntityType>(BuildDbColumnName(primaryKeys[0]) + " = " + BuildDbParamName(primaryKeys[0])), new object[] { id }, tran);
        }

        public void Delete<IEntityType>(string where, object[] paramValues)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot delete.");
            }

            Delete<IEntityType>(where, paramValues, null);
        }

        public void Delete<IEntityType>(WhereClip where)
            where IEntityType : IEntity
        {
            Delete<IEntityType>(ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public void Delete<IEntityType>(string where, object[] paramValues, DbTransaction tran)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot delete.");
            }

            string tableName = Entity<IEntityType>.GetUpdateTableName() ?? Entity<IEntityType>.GetTableName();
            SimpleDbHelper.Delete(db, ParseTableName(tableName), BuildCombinedWhere<IEntityType>(where), paramValues, tran);
        }

        public void Delete<IEntityType>(WhereClip where, DbTransaction tran)
            where IEntityType : IEntity
        {
            Delete<IEntityType>(ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues, tran);
        }

        public void CascadeDelete<IEntityType>(IEntityType obj)
            where IEntityType : IEntity
        {
            CascadeDelete<IEntityType>(obj, null);
        }

        public void CascadeDelete<IEntityType>(IEntityType obj, DbTransaction tran)
            where IEntityType : IEntity
        {
            if (obj == null)
            {
                return;
            }

            new ActiveEntity<IEntityType>(this, obj, ActiveEntityStatus.Loaded).Delete(tran);
        }

        public void CascadeDelete<IEntityType>(params object[] pkValues)
            where IEntityType : IEntity
        {
            CascadeDelete<IEntityType>(null, pkValues);
        }

        public void CascadeDelete<IEntityType>(DbTransaction tran, params object[] pkValues)
            where IEntityType : IEntity
        {
            if (pkValues != null && pkValues.Length > 0)
            {
                new ActiveEntity<IEntityType>(this, pkValues).Delete(tran);
            }
        }

        public int Insert<IEntityType>(IEntityType obj, params string[] exceptColumns)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot insert.");
            }

            return Insert<IEntityType>(obj, null, exceptColumns);
        }

        public int Insert<IEntityType>(IEntityType obj, DbTransaction tran, params string[] exceptColumns)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot insert.");
            }

            string tableName = Entity<IEntityType>.GetUpdateTableName() ?? Entity<IEntityType>.GetTableName();
            string[] insertColumns = Entity<IEntityType>.GetInsertColumnNames(exceptColumns);
            string[] autoKeys = Entity<IEntityType>.GetReadOnlyColumnNames();
            int retVal;
            if (autoKeys != null && autoKeys.Length > 0)
            {
                retVal = SimpleDbHelper.Insert(db, ParseTableName(tableName), insertColumns, obj.GetKeyValues().GetValues(insertColumns), tran, autoKeys[0], Entity<IEntityType>.GetTableAdditionalInsert());
                if (retVal > 0)
                {
                    obj.GetKeyValues()[autoKeys[0]] = Convert.ChangeType(retVal, obj.GetKeyValues().GetTypes(autoKeys)[0]);
                }
            }
            else
            {
                retVal = SimpleDbHelper.Insert(db, ParseTableName(tableName), insertColumns, obj.GetKeyValues().GetValues(insertColumns), tran, null, Entity<IEntityType>.GetTableAdditionalInsert());
            }

            return retVal;
        }

        public void CascadeInsert<IEntityType>(IEntityType obj)
            where IEntityType : IEntity
        {
            CascadeInsert<IEntityType>(obj, null);
        }

        public void CascadeInsert<IEntityType>(IEntityType obj, DbTransaction tran)
            where IEntityType : IEntity
        {
            if (obj == null)
            {
                return;
            }

            new ActiveEntity<IEntityType>(this, obj, ActiveEntityStatus.New).Save(tran);
        }

        public void Update<IEntityType>(IEntityType obj, params string[] exceptColumns)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot update.");
            }

            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length == 0)
            {
                throw new NotSupportedException("Gateway.Update<IEntityType>(IEntityType obj, DbTransaction tran, params string[] exceptColumns) only supports entities with at least one primary key.");
            }

            DoUpdate<IEntityType>(obj, null, exceptColumns, primaryKeys);
        }

        public void Update<IEntityType>(IEntityType obj, DbTransaction tran, params string[] exceptColumns)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot update.");
            }

            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length == 0)
            {
                throw new NotSupportedException("Gateway.Update<IEntityType>(IEntityType obj, DbTransaction tran, params string[] exceptColumns) only supports entities with at least one primary key.");
            }

            DoUpdate<IEntityType>(obj, tran, exceptColumns, primaryKeys);
        }

        public void CascadeUpdate<IEntityType>(IEntityType obj)
            where IEntityType : IEntity
        {
            CascadeUpdate<IEntityType>(obj, null);
        }

        public void CascadeUpdate<IEntityType>(IEntityType obj, DbTransaction tran)
            where IEntityType : IEntity
        {
            if (obj == null)
            {
                return;
            }

            new ActiveEntity<IEntityType>(this, obj, ActiveEntityStatus.Loaded).Save(tran);
        }

        public void Update<IEntityType>(string[] columns, object[] values, object id)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot update.");
            }

            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Update<IEntityType>(string[] columns, object[] values, object id) only supports entities with single primary key.");
            }
            DoUpdate<IEntityType>(columns, values, id, null, primaryKeys);
        }

        public void Update<IEntityType>(string[] columns, object[] values, object id, DbTransaction tran)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot update.");
            }

            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Update<IEntityType>(string[] columns, object[] values, object id, DbTransaction tran) only supports entities with single primary key.");
            }
            DoUpdate<IEntityType>(columns, values, id, tran, primaryKeys);
        }

        public void Update<IEntityType>(string[] columns, object[] values, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot update.");
            }

            Update<IEntityType>(columns, values, where, paramValues, null);
        }

        public void Update<IEntityType>(string[] columns, object[] values)
            where IEntityType : IEntity
        {
            Update<IEntityType>(columns, values, null, new object[0]);
        }

        public void Update<IEntityType>(string[] columns, object[] values, WhereClip where)
            where IEntityType : IEntity
        {
            Update<IEntityType>(columns, values, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public void Update<IEntityType>(string[] columns, object[] values, string where, object[] whereValues, DbTransaction tran)
            where IEntityType : IEntity
        {
            if (Entity<IEntityType>.IsView() && Entity<IEntityType>.GetUpdateTableName() == null)
            {
                throw new NotSupportedException("View cannot update.");
            }

            DoUpdate<IEntityType>(columns, values, where, whereValues, tran);
        }

        public void Update<IEntityType>(string[] columns, object[] values, WhereClip where, DbTransaction tran)
            where IEntityType : IEntity
        {
            Update<IEntityType>(columns, values, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues, tran);
        }

        public IEntityType[] SelectAll<IEntityType>()
            where IEntityType : IEntity
        {
            return SelectAll<IEntityType>((string)null);
        }

        public IEntityType[] SelectAll<IEntityType>(string orderBy)
            where IEntityType : IEntity
        {
            return Select<IEntityType>(null, orderBy, null);
        }

        public IEntityType[] SelectAll<IEntityType>(OrderByClip orderBy)
            where IEntityType : IEntity
        {
            return SelectAll<IEntityType>(ParseConcreteWhereOrderBy(orderBy.ToString()));
        }

        public IEntityType[] SelectAll<IEntityType>(int pageSize, int pageNo)
            where IEntityType : IEntity
        {
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length == 0)
            {
                primaryKeys = GuessPrimaryKeysForSelectPage<IEntityType>();
            }
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.SelectAll<IEntityType>(string orderBy, int pageSize, int pageNo) only supports entities with single primary key.");
            }
            return DoSelectPage<IEntityType>(null, null, null, pageSize, pageNo, primaryKeys);
        }

        public IEntityType[] SelectAll<IEntityType>(string orderBy, int pageSize, int pageNo)
            where IEntityType : IEntity
        {
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length == 0)
            {
                primaryKeys = GuessPrimaryKeysForSelectPage<IEntityType>();
            }
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.SelectAll<IEntityType>(string orderBy, int pageSize, int pageNo) only supports entities with single primary key.");
            }
            return DoSelectPage<IEntityType>(orderBy, null, null, pageSize, pageNo, primaryKeys);
        }

        public IEntityType[] SelectAll<IEntityType>(OrderByClip orderBy, int pageSize, int pageNo)
            where IEntityType : IEntity
        {
            return SelectAll<IEntityType>(ParseConcreteWhereOrderBy(orderBy.ToString()), pageSize, pageNo);
        }

        public IEntityType[] Select<IEntityType>(string where, object[] paramValues)
            where IEntityType : IEntity
        {
            return Select<IEntityType>(where, null, paramValues);
        }

        public IEntityType[] Select<IEntityType>(WhereClip where)
            where IEntityType : IEntity
        {
            return Select<IEntityType>(ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public IEntityType[] Select<IEntityType>(string where, string orderBy, object[] paramValues)
            where IEntityType : IEntity
        {
            IDataReader reader = SimpleDbHelper.SelectReadOnly(db, ParseTableName(Entity<IEntityType>.GetTableName()), Entity<IEntityType>.GetColumnNames(), BuildCombinedWhere<IEntityType>(where), paramValues, orderBy);
            IEntityType[] objs = EntityFactory<IEntityType>.CreateObjectList(reader, true);
            reader.Close();
            return objs;
        }

        public IEntityType[] Select<IEntityType>(WhereClip where, OrderByClip orderBy)
            where IEntityType : IEntity
        {
            return Select<IEntityType>(ParseConcreteWhereOrderBy(where.ToString()), ParseConcreteWhereOrderBy(orderBy.ToString()), where.ParamValues);
        }

        public IEntityType[] Select<IEntityType>(int pageSize, int pageNo, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length == 0)
            {
                primaryKeys = GuessPrimaryKeysForSelectPage<IEntityType>();
            }
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Select<IEntityType>(int pageSize, int pageNo, string where, object[] paramValues) only supports entities with single primary key.");
            }
            return DoSelectPage<IEntityType>(null, where, paramValues, pageSize, pageNo, primaryKeys);
        }

        public IEntityType[] Select<IEntityType>(int pageSize, int pageNo)
            where IEntityType : IEntity
        {
            return SelectAll<IEntityType>(pageSize, pageNo);
        }

        public IEntityType[] Select<IEntityType>(int pageSize, int pageNo, WhereClip where)
            where IEntityType : IEntity
        {
            return Select<IEntityType>(pageSize, pageNo, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public IEntityType[] Select<IEntityType>(int pageSize, int pageNo, string where, string orderBy, object[] paramValues)
            where IEntityType : IEntity
        {
            string[] primaryKeys = Entity<IEntityType>.GetPrimaryKeyColumnNames();
            if (primaryKeys.Length == 0)
            {
                primaryKeys = GuessPrimaryKeysForSelectPage<IEntityType>();
            }
            if (primaryKeys.Length != 1)
            {
                throw new NotSupportedException("Gateway.Select<IEntityType>(int pageSize, int pageNo, string where, string orderBy, object[] paramValues) only supports entities with single primary key.");
            }
            return DoSelectPage<IEntityType>(orderBy, where, paramValues, pageSize, pageNo, primaryKeys);
        }

        public IEntityType[] Select<IEntityType>(int pageSize, int pageNo, WhereClip where, OrderByClip orderBy)
            where IEntityType : IEntity
        {
            return Select<IEntityType>(pageSize, pageNo, ParseConcreteWhereOrderBy(where.ToString()), ParseConcreteWhereOrderBy(orderBy.ToString()), where.ParamValues);
        }

        public int SelectRowCount<IEntityType>(string where, object[] paramValues)
            where IEntityType : IEntity
        {
            object obj = SelectAggregateValue<IEntityType>(where, paramValues, "count(*)");
            if (obj == DBNull.Value)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }

        public int SelectRowCount<IEntityType>(WhereClip where)
            where IEntityType : IEntity
        {
            return SelectRowCount<IEntityType>(ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public object SelectMax<IEntityType>(string columnName, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            return SelectAggregateValue<IEntityType>(where, paramValues, string.Format("max({0})", columnName));
        }

        public object SelectMax<IEntityType>(string columnName, WhereClip where)
            where IEntityType : IEntity
        {
            return SelectMax<IEntityType>(columnName, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public object SelectMin<IEntityType>(string columnName, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            return SelectAggregateValue<IEntityType>(where, paramValues, string.Format("min({0})", columnName));
        }

        public object SelectMin<IEntityType>(string columnName, WhereClip where)
            where IEntityType : IEntity
        {
            return SelectMin<IEntityType>(columnName, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public object SelectSum<IEntityType>(string columnName, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            return SelectAggregateValue<IEntityType>(where, paramValues, string.Format("sum({0})", columnName));
        }

        public object SelectSum<IEntityType>(string columnName, WhereClip where)
            where IEntityType : IEntity
        {
            return SelectSum<IEntityType>(columnName, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public object SelectAvg<IEntityType>(string columnName, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            return SelectAggregateValue<IEntityType>(where, paramValues, string.Format("avg({0})", columnName));
        }

        public object SelectAvg<IEntityType>(string columnName, WhereClip where)
            where IEntityType : IEntity
        {
            return SelectAvg<IEntityType>(columnName, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public ReturnColumnType[] SelectSingleColumn<IEntityType, ReturnColumnType>(string columnName, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            return SelectSingleColumn<IEntityType, ReturnColumnType>(columnName, where, null, paramValues);
        }

        public ReturnColumnType[] SelectSingleColumn<IEntityType, ReturnColumnType>(string columnName, WhereClip where)
            where IEntityType : IEntity
        {
            return SelectSingleColumn<IEntityType, ReturnColumnType>(columnName, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public ReturnColumnType[] SelectSingleColumn<IEntityType, ReturnColumnType>(string columnName, string where, string orderBy, object[] paramValues)
            where IEntityType : IEntity
        {
            IDataReader reader = SimpleDbHelper.SelectReadOnly(db, ParseTableName(Entity<IEntityType>.GetTableName()), new string[] { columnName }, BuildCombinedWhere<IEntityType>(where), paramValues, orderBy);
            List<ReturnColumnType> list = new List<ReturnColumnType>();
            while (reader.Read())
            {
                list.Add((ReturnColumnType)reader.GetValue(0));
            }
            reader.Close();
            return list.ToArray();
        }

        public ReturnColumnType[] SelectSingleColumn<IEntityType, ReturnColumnType>(string columnName, WhereClip where, OrderByClip orderBy)
            where IEntityType : IEntity
        {
            return SelectSingleColumn<IEntityType, ReturnColumnType>(columnName, ParseConcreteWhereOrderBy(where.ToString()), ParseConcreteWhereOrderBy(orderBy.ToString()), where.ParamValues);
        }

        public ReturnColumnType SelectScalar<IEntityType, ReturnColumnType>(string columnName, string where, object[] paramValues)
            where IEntityType : IEntity
        {
            IDataReader reader = SimpleDbHelper.SelectReadOnly(db, ParseTableName(Entity<IEntityType>.GetTableName()), new string[] { columnName }, BuildCombinedWhere<IEntityType>(where), paramValues, null);
            object obj = null;
            if (reader.Read())
            {
                obj = reader.GetValue(0);
            }
            reader.Close();

            if (obj == DBNull.Value)
            {
                obj = null;
            }

            if (typeof(IEntityType).IsValueType)
            {
                obj = 0;
            }

            return (ReturnColumnType)obj;
        }

        public ReturnColumnType SelectScalar<IEntityType, ReturnColumnType>(string columnName, WhereClip where)
            where IEntityType : IEntity
        {
            return SelectScalar<IEntityType, ReturnColumnType>(columnName, ParseConcreteWhereOrderBy(where.ToString()), where.ParamValues);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>()
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(null, new object[0]);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults)
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(null, new object[0], outParamNames, outParamTypes, out outParamResults);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(DbTransaction tran)
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(null, tran);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults, DbTransaction tran)
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(null, outParamNames, outParamTypes, out outParamResults, tran);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(string[] paramNames, object[] paramValues)
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(paramNames, paramValues, null);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults)
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(paramNames, paramValues, outParamNames, outParamTypes, out outParamResults, null);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(string[] paramNames, object[] paramValues, DbTransaction tran)
            where IEntityType : IEntity
        {
            IDataReader reader = ExecuteProcedureReadOnly(Entity<IEntityType>.GetStoreProcedureName(), paramNames, paramValues, tran);
            IEntityType[] objs = EntityFactory<IEntityType>.CreateObjectList(reader, Entity<IEntityType>.GetStoreProcedureFillByIndex());
            reader.Close();
            return objs;
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults, DbTransaction tran)
            where IEntityType : IEntity
        {
            IDataReader reader = ExecuteProcedureReadOnly(Entity<IEntityType>.GetStoreProcedureName(), paramNames, paramValues, outParamNames, outParamTypes, out outParamResults, tran);
            IEntityType[] objs = EntityFactory<IEntityType>.CreateObjectList(reader, Entity<IEntityType>.GetStoreProcedureFillByIndex());
            reader.Close();
            return objs;
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(WhereClip where)
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(where, null);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(WhereClip where, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults)
            where IEntityType : IEntity
        {
            return ExecuteProcedure<IEntityType>(where, outParamNames, outParamTypes, out outParamResults, null);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(WhereClip where, DbTransaction tran)
           where IEntityType : IEntity
        {
            string[] paramNames = null;
            object[] paramValues = null;

            if (where != null)
            {
                paramNames = db.DbProvider.DiscoverParams(ParseConcreteWhereOrderBy(where.ToString()));
                paramValues = where.ParamValues;
            }

            return ExecuteProcedure<IEntityType>(paramNames, paramValues, tran);
        }

        public IEntityType[] ExecuteProcedure<IEntityType>(WhereClip where, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults, DbTransaction tran)
           where IEntityType : IEntity
        {
            string[] paramNames = null;
            object[] paramValues = null;

            if (where != null)
            {
                paramNames = db.DbProvider.DiscoverParams(ParseConcreteWhereOrderBy(where.ToString()));
                paramValues = where.ParamValues;
            }

            return ExecuteProcedure<IEntityType>(paramNames, paramValues, outParamNames, outParamTypes, out outParamResults, tran);
        }

        #endregion

        #region Non-StrongTyped Gateways

        public DataSet SelectDataSet(string cmdText, object[] paramValues)
        {
            return SimpleDbHelper.Select(db, cmdText, paramValues);
        }

        public IDataReader SelectDataReader(string cmdText, object[] paramValues)
        {
            return SimpleDbHelper.SelectReadOnly(db, cmdText, paramValues);
        }

        public void ExecuteNonQuery(string cmdText, object[] paramValues)
        {
            SimpleDbHelper.ExecuteNonQuery(db, cmdText, paramValues);
        }

        public void ExecuteNonQuery(string cmdText, DbTransaction tran, object[] paramValues)
        {
            SimpleDbHelper.ExecuteNonQuery(db, cmdText, tran, paramValues);
        }

        public ReturnColumnType SelectScalar<ReturnColumnType>(string cmdText, object[] paramValues)
        {
            object retVal = SimpleDbHelper.SelectScalar(db, cmdText, paramValues);
            if (retVal == DBNull.Value)
            {
                retVal = default(ReturnColumnType);
            }
            return (ReturnColumnType)retVal;
        }

        public DataSet ExecuteProcedure(string procedureName, string[] paramNames, object[] paramValues)
        {
            return SimpleDbHelper.ExecuteProcedure(db, procedureName, paramNames, paramValues);
        }

        public DataSet ExecuteProcedure(string procedureName, string[] paramNames, object[] paramValues, DbTransaction tran)
        {
            return SimpleDbHelper.ExecuteProcedure(db, procedureName, paramNames, paramValues, tran);
        }

        public IDataReader ExecuteProcedureReadOnly(string procedureName, string[] paramNames, object[] paramValues)
        {
            return SimpleDbHelper.ExecuteProcedureReadOnly(db, procedureName, paramNames, paramValues);
        }

        public IDataReader ExecuteProcedureReadOnly(string procedureName, string[] paramNames, object[] paramValues, DbTransaction tran)
        {
            return SimpleDbHelper.ExecuteProcedureReadOnly(db, procedureName, paramNames, paramValues, tran);
        }

        public object ExecuteProcedureScalar(string procedureName, string[] paramNames, object[] paramValues)
        {
            return SimpleDbHelper.ExecuteProcedureScalar(db, procedureName, paramNames, paramValues);
        }

        public object ExecuteProcedureScalar(string procedureName, string[] paramNames, object[] paramValues, DbTransaction tran)
        {
            return SimpleDbHelper.ExecuteProcedureScalar(db, procedureName, paramNames, paramValues, tran);
        }

        public void ExecuteProcedureNonQuery(string procedureName, string[] paramNames, object[] paramValues)
        {
            SimpleDbHelper.ExecuteProcedureNonQuery(db, procedureName, paramNames, paramValues);
        }

        public void ExecuteProcedureNonQuery(string procedureName, string[] paramNames, object[] paramValues, DbTransaction tran)
        {
            SimpleDbHelper.ExecuteProcedureNonQuery(db, procedureName, paramNames, paramValues, tran);
        }

        public DataSet ExecuteProcedure(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults)
        {
            return SimpleDbHelper.ExecuteProcedure(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults);
        }

        public DataSet ExecuteProcedure(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults, DbTransaction tran)
        {
            return SimpleDbHelper.ExecuteProcedure(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults, tran);
        }

        public IDataReader ExecuteProcedureReadOnly(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults)
        {
            return SimpleDbHelper.ExecuteProcedureReadOnly(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults);
        }

        public IDataReader ExecuteProcedureReadOnly(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults, DbTransaction tran)
        {
            return SimpleDbHelper.ExecuteProcedureReadOnly(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults, tran);
        }

        public object ExecuteProcedureScalar(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults)
        {
            return SimpleDbHelper.ExecuteProcedureScalar(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults);
        }

        public object ExecuteProcedureScalar(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults, DbTransaction tran)
        {
            return SimpleDbHelper.ExecuteProcedureScalar(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults, tran);
        }

        public void ExecuteProcedureNonQuery(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults)
        {
            SimpleDbHelper.ExecuteProcedureNonQuery(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults);
        }

        public void ExecuteProcedureNonQuery(string procedureName, string[] paramNames, object[] paramValues, string[] outParamNames, DbType[] outParamTypes, out object[] outParamResults, DbTransaction tran)
        {
            SimpleDbHelper.ExecuteProcedureNonQuery(db, procedureName, paramNames, paramValues, outParamNames, outParamTypes, out outParamResults, tran);
        }

        #endregion
    }
}
